package com.gipson.amc.utils.excelprocess;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.primitives.Bytes;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Map;
import java.util.UUID;


public class ExcelUtil {
    /**
     * 创建excel文档，
     *  list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static HSSFWorkbook createWorkBook(JSONArray list, String []keys, String columnNames[]) {
        // 创建excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet页，并命名
        //HSSFSheet sheet = wb.createSheet(DateUtils.date2Str(new Date(), "yyyyMMdd") + System.currentTimeMillis()+UUID.randomUUID());
        HSSFSheet sheet = wb.createSheet(System.currentTimeMillis()+""+UUID.randomUUID()+"");
        // 设置列宽
        for(int i=0;i<keys.length;i++){
            //最后一列为附件URL地址,列宽设置大一些
            if(i==(keys.length-1)){
                sheet.setColumnWidth((short) i, (short) (200*120));
            }else{
                sheet.setColumnWidth((short) i, (short) (50*60));
            }
        }

        // 创建第一行，并设置其单元格格式
        HSSFRow row = sheet.createRow((short) 0);
        row.setHeight((short)500);
        // 单元格格式(用于列名)
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        f.setFontName("宋体");
        f.setFontHeightInPoints((short) 10);
        cs.setFont(f);
        cs.setLocked(true);
        cs.setWrapText(true);//自动换行
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        //设置首行外,每行每列的值(Row和Cell都从0开始)
        for (short i = 1; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow((short) i);
            String flag = "";
            //在Row行创建单元格
            for(short j=0;j<keys.length;j++){
                HSSFCell cell = row1.createCell(j);
                cell.setCellValue(list.getJSONObject(i).get(keys[j]) == null?" ": list.getJSONObject(i).get(keys[j]).toString());
            }
        }
        return wb;
    }

     /**
       * @description 生成并下载Excel
       * @param list 数据
       * @param keys list中map的key数组集合
       * @param columnNames excel的列名
       * @param fileName 文件名
       * @author: xsyuan 2020/4/16 16:04
       * @return
       */
    public static void downloadWorkBook(JSONArray list, String keys[], String columnNames[], String fileName, HttpServletResponse response) throws IOException{
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
     /**
       * @description 生成并下载Excel
       * @param list 数据
       * @param keys list中map的key数组集合
       * @param columnNames excel的列名
       * @param fileName 文件名
       * @author: xsyuan 2020/4/16 16:02
       * @return
       */
    public static  void downloadCsv(JSONArray list, String keys[], String columnNames[], String fileName, HttpServletResponse response) throws IOException{
    	StringBuffer write = new StringBuffer();
    	String enter = "\r\n";
    	if(columnNames!=null){
    		for(int i=0;i<columnNames.length;i++){
    			write.append(columnNames[i]);
    			if(i<columnNames.length-1){
    				write.append(",");
    			}else{
    				write.append(enter);
    			}
        	}
    	}
    	if(keys!=null && list!=null){
    		for(int i=0;i<list.size();i++){
    			JSONObject json=list.getJSONObject(i);
    			//Map<String, Object> map=JSONUtils.json2map(json);
    			Map<String, Object> map=json;
    			for(int j=0;j<keys.length;j++){
    				String key=keys[j];
    				if(map.containsKey(key)){
    					Object param=map.get(key);
    					String value=object2Str(param);
    					if(value.contains(",")){
    						value=value.replaceAll(",", "，");
    					}
    					if(value.contains("\r\n")){
    						value=value.replaceAll("\r\n", "。");
    					}
    					write.append(value);
    				}else{
    					write.append(" ");
    				}
    				write.append(",");
    			}
    			write.append(enter);

        	}
    	}
    	String write_tmp2=new String(write);
    	String write_tmp=new String(write_tmp2.getBytes("UTF-8"), "GBK");
    	byte commonCsvHead[] = {(byte) 0xEF, (byte) 0xBB,(byte) 0xBF};
        InputStream is = new   ByteArrayInputStream(Bytes.concat(commonCsvHead,write_tmp2.getBytes()));
        // 设置response参数
        response.reset();
        response.setContentType("text/plain;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".csv").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
    /**
     * 对象转字符串（生产文件）
     */
    public static String object2Str(Object param) {
        if(param==null){
            return "";
        }
        return "\t"+String.valueOf(param);
    }
}

